package com.csi.sai.bl.recruitment;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.sql.DataSource;

import net.sf.json.JSONArray;

import com.csi.sai.bl.dataview.GetDataView;
import com.csi.sai.bl.dataview.GetDataViewFactory;
import com.csi.sai.data.entities.Candidato;
import com.csi.sai.data.entities.Demand;
import com.csi.sai.data.entities.Types;
import com.csi.sai.data.entities.Vacancy;
import com.csi.sai.utilities.Constants;
import com.csi.sai.utilities.GenericEntitie;
import com.csi.sai.utilities.Utilities;

public class RecruitmentProcess {

	private DataSource ds = null;
	private String folio;
	private int idVacancy;
	private Vacancy vacancy;
	private String HTMLCodeTraincing;
	private String HTMLGenerated;
	private int idGenerated;
	
	public String getFolio()			{ return this.folio; 	}
	public Vacancy getVacancy() 		{ return this.vacancy; 	}
	public int getIdVacancy() 			{ return this.idVacancy;}
	public String getHTMLCodeTraicing()	{ return this.HTMLCodeTraincing; }
	public int getIdGenerated()			{ return this.idGenerated; }
	public String getHTMLGenerated()	{ return this.HTMLGenerated; }
	
	public RecruitmentProcess(DataSource ds) {
		this.ds = ds;
	}
	public boolean createVacancy(int type, int idDemand, String finishDate, String vacantesREstantes) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_RECTRUITMENT);
			Vacancy v = new Vacancy();
			v.setIdDemand(idDemand);
			v.setFinishDate(finishDate);
			v.setvRestantes(Integer.parseInt(vacantesREstantes));
			v.setEstatus(Constants.STS_VACANCY_PROCESS);
			v.setComentarios("Creacion Vacante");
			v.setCreation(Utilities.getTodayByFormat(Utilities.formatDB));
			Utilities.generatePreparedStatement(ps, type, v);
			rs = ps.executeQuery();
			rs.next();
			folio = rs.getString(10);
			idVacancy = rs.getInt(1);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}

	public boolean createCandidate(int type, Candidato c) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int stype;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_CANDIDATE);
			if(type==1) {
				c.setFechaAlta(Utilities.getTodayByFormat(Utilities.formatDB));
				c.setEstatus(Constants.STS_ACTIVE);
			}
			Utilities.generatePreparedStatement(ps, type, c);
			rs = ps.executeQuery();
			
			rs.next();
			idGenerated = rs.getInt(1);
			
			rs.close();
			ps.close();
			
			/** Se crean mails relacionados al Candidato **/
			for(String mail : c.getCorreo()) {
				stype = mail.split("_")[2].equals("0") ? Constants.TRAN_TYP_CREATEDATA : Constants.TRAN_TYP_UPDATEDATA;
				
				Types t = new Types();
				ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
				
				t.setEntity("correo");
				t.setIdType(Integer.parseInt(mail.split("_")[0]));
				t.setData(mail.split("_")[1]);
				t.setIdCandidate(idGenerated);
				if(stype==5) {
					t.setIdData(Integer.parseInt(mail.split("_")[2]));
				}
				Utilities.generatePreparedStatement(ps, stype, t);
				
				ps.execute();
				ps.close();
			}
			
			/** Se crean telefonos relacionados al Candidato **/
			for(String phone : c.getTelefono()) {
				stype = phone.split("_")[2].equals("0") ? Constants.TRAN_TYP_CREATEDATA : Constants.TRAN_TYP_UPDATEDATA;
				
				Types t = new Types();
				ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
				
				t.setEntity("telefono");
				t.setIdType(Integer.parseInt(phone.split("_")[0]));
				t.setData(phone.split("_")[1]);
				t.setIdCandidate(idGenerated);
				if(stype==5) {
					t.setIdData(Integer.parseInt(phone.split("_")[2]));
				}
				Utilities.generatePreparedStatement(ps, stype, t);
				
				ps.execute();
				ps.close();
			}
			
			/** Se crean las tecnologias relacionadas al Candidato **/
			for(String tech : c.getTecnologia()) {
				stype = tech.split("_")[2].equals("0") ? Constants.TRAN_TYP_CREATEDATA : Constants.TRAN_TYP_UPDATEDATA;
				
				Types t = new Types();
				ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
				
				t.setEntity("tecnologia");
				t.setIdType(Integer.parseInt(tech.split("_")[0]));
				t.setData(tech.split("_")[1]);
				t.setIdCandidate(idGenerated);
				t.setDate(Utilities.getTodayByFormat(Utilities.formatDB));
				if(stype==5) {
					t.setIdData(Integer.parseInt(tech.split("_")[2]));
				}
				Utilities.generatePreparedStatement(ps, stype, t);
				
				ps.execute();
				ps.close();
			}
			
			/** Se obtienen los registos recien insertados **/
			Types t = new Types();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_TYPES);
			t.setIdCandidate(idGenerated);
			t.setEntity(Constants.TRAN_TYP_ENTCANDIDATE);
			Utilities.generatePreparedStatement(ps, 6, t);
			
			rs = ps.executeQuery();
			
			/** El parametro 3 indica que es candidato **/
			this.HTMLGenerated = Utilities.getEntitiesEditMode(rs, ps, conn, 3);
			System.out.println(HTMLGenerated);
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	public boolean getVacancyTraicing(int type, int idVacancy) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_RECTRUITMENT);
			Vacancy v = new Vacancy();
			v.setIdVacancy(idVacancy);
			Utilities.generatePreparedStatement(ps, type, v);
			rs = ps.executeQuery();
			
			this.vacancy = new Vacancy();
			while(rs.next()) {
				vacancy.setFolio(rs.getString("Folio"));
				vacancy.setEstatus(rs.getString(1));
				vacancy.setDateCreation(rs.getString("FechaCreacion"));
				vacancy.setContact(rs.getString("Contacto"));
				vacancy.setProfile(rs.getString("Perfil"));
				vacancy.setVacancy(rs.getString("Vacante"));
				vacancy.setIdDemand(rs.getInt("idsolicitud"));
				vacancy.setCreation(rs.getString("FechaAlta"));
				vacancy.setFinshDate(rs.getString(9));
				vacancy.setPriorityDate(rs.getString("FechaPrioridad"));
				vacancy.setDuration(rs.getInt("Duracion"));
				vacancy.setMensualRate(rs.getInt("Tarifa"));
				vacancy.setClientName(rs.getString("NombreCliente"));
				vacancy.setSalesPerson(rs.getString("NombreEmpleado")+" "+rs.getString("ApellidoPaterno"));
				vacancy.setProcessDays(Utilities.getDaysFromADate(rs.getString("FechaAlta")));
				break;
			}
			
			/*** Se obtienen los candidatos relacionados **/
			GetDataView dataView = GetDataViewFactory.getDataView(5, ds);
			boolean succesfully = dataView.getVacancyTraicingWithCandidatesRelationship(5, idVacancy);
			if(succesfully) {
				this.HTMLCodeTraincing = dataView.getHTMLCodeTraicing();
			} else {
				this.HTMLCodeTraincing = "Error al cargar los candidatos relacionados";
			}
			 
			System.out.println(this.HTMLCodeTraincing);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	public <T> boolean createRelationshipCandVac(int idVacancy, GenericEntitie<T> objFoundCands, int userSession) {
		/**
		 * Las listas de encontrados, interesados y postulados (0,1,2) viene solamente el id de la vacante
		 * La lista de comentarios (3) viene en la forma : idCandidato_Comentario
		 * **/
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_RECTRUITMENT);
			ArrayList<String> listComments = (ArrayList<String>)objFoundCands.getgObjectList().get(3);
			for(int index=0; index<(objFoundCands.getgObjectList().size()-1); index++) {
				Vacancy v = new Vacancy();
				ArrayList<String> actualList = (ArrayList<String>)objFoundCands.getgObjectList().get(index);
				v.setIdVacancy(idVacancy);
				for(int idx=0; idx<actualList.size(); idx++) {
					v.setIdCandidate(Integer.parseInt(actualList.get(idx)));
					v.setComment(findCommentsToArray(Integer.parseInt(actualList.get(idx)), listComments));
					v.setEstatus(index==0 ? Constants.STS_FOUND : (index==1 ? Constants.STS_STAKEHOLDER : Constants.STS_POSTULATE));
					v.setIdEmpleado(userSession);
					v.setIsEmployee(0);
					v.setDateCreation(Utilities.getTodayByFormat(Utilities.formatDB));
					Utilities.generatePreparedStatement(ps, 2, v);
					ps.execute();
				}
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	public boolean acceptDemand(int type, int demandToUpdate, int idEmpleado) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_DEMAND);
			Demand demand = new Demand();
			demand.setIdDemand(demandToUpdate);
			demand.setDuration(1);
			demand.setFinshDate(Utilities.getTodayByFormat(Utilities.formatDB));
			demand.setStatus(Constants.STS_DEMAND_PROCESS);
			demand.setProfile("Solicitud aceptada");
			demand.setIdSalesperson(idEmpleado);
			Utilities.generatePreparedStatement(ps, type, demand);
			
			ps.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	public boolean denyDemand(int type, String comment, int demandToDeny, int idEmpleado) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			ps = conn.prepareStatement(Constants.CALL_PROCEDURE_DEMAND);
			Demand demand = new Demand();
			demand.setIdDemand(demandToDeny);
			demand.setDuration(0);
			demand.setProfile(comment);
			demand.setFinshDate(Utilities.getTodayByFormat(Utilities.formatDB));
			demand.setStatus(Constants.STS_DEMAND_DENY);
			demand.setIdSalesperson(idEmpleado);
			Utilities.generatePreparedStatement(ps, type, demand);
			
			ps.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Utilities.finallyProcess(conn, ps, rs);
			Utilities.finallyConnection(conn);
		}
		return false;
	}
	
	private String findCommentsToArray(int idCandidate,
			ArrayList<String> listComments) {
		for(int index=0; index<listComments.size(); index++) {
			int actualCandidate = Integer.parseInt(listComments.get(index).split("_")[0]);
			if(actualCandidate == idCandidate) {
				return listComments.get(index).split("_")[1];
			}
		}
		return "";
	}
}
